﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RCA1._0;
using System.Data;
using System.Data.OleDb;

namespace Royal_1._0_DAL_WS
{
    public class DieuLeDAO : DataProvider
    {
        public DataTable LayBang()
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from DIEULE";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        #region "Lấy đối tượng"
        private DieuLeDTO LayDoiTuong(String sql)
        {
            OleDbConnection cn = this.Connect();

            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();

            DieuLeDTO tempDTO = null;
            if (dr.Read())
            {
                tempDTO = new DieuLeDTO();
                tempDTO.IDDieuLe = Convert.ToInt32(dr["IDDieuLe"]);
                tempDTO.MaChuong = Convert.ToInt32(dr["MaChuong"]);
                tempDTO.TenDieuLe = dr["TenDieuLe"].ToString();
                tempDTO.SoDieuLe = dr["SoDieuLe"].ToString();
                tempDTO.SoDieuLe = dr["NoiDung"].ToString();

            }
            cn.Close();
            return tempDTO;
        }

        #endregion


        #region "Lấy danh sách"
        private List<DieuLeDTO> LayDSach(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DieuLeDTO tempDTO;
            List<DieuLeDTO> dsDIEULE = new List<DieuLeDTO>();
            while (dr.Read())
            {
                tempDTO = new DieuLeDTO();
                tempDTO.IDDieuLe = Convert.ToInt32(dr["IDDieuLe"]);
                tempDTO.MaChuong = Convert.ToInt32(dr["MaChuong"]);
                tempDTO.TenDieuLe = dr["TenDieuLe"].ToString();
                tempDTO.SoDieuLe = dr["SoDieuLe"].ToString();
                tempDTO.SoDieuLe = dr["NoiDung"].ToString();
                dsDIEULE.Add(tempDTO);
            }
            return dsDIEULE;
        }

        public List<DieuLeDTO> LayDanhSach()
        {
            String sql = "Select * From DIEULE ";
            return LayDSach(sql);
        }

        public List<DieuLeDTO> LayDanhSachDieuLeTheoMaChuong(int MaChuong)
        {
            String sql = "Select * From DIEULE Where MaChuong = " + MaChuong;
            return LayDSach(sql);
        }
      
        #endregion


        #region "Lấy danh sách tên"
        private List<DieuLeDTO> LayDSachTen(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DieuLeDTO tempDTO;
            List<DieuLeDTO> dsDIEULE = new List<DieuLeDTO>();
            while (dr.Read())
            {
                tempDTO = new DieuLeDTO();
                tempDTO.TenDieuLe = dr["TenDieuLe"].ToString();
                dsDIEULE.Add(tempDTO);
            }
            return dsDIEULE;
        }

        public List<DieuLeDTO> LayDanhSachTen()
        {
            String sql = "Select * From DIEULE ";
            return LayDSachTen(sql);
        }

        public List<DieuLeDTO> LayDanhSachTenDieuLeTheoMaChuong(int MaChuong)
        {
            String sql = "Select * From DIEULE Where MaChuong = " + MaChuong;
            return LayDSachTen(sql);
        }
      
        #endregion


        #region "Lấy danh sách số điều lệ"
        private List<DieuLeDTO> LayDSachSoDieuLe(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DieuLeDTO tempDTO;
            List<DieuLeDTO> dsDIEULE = new List<DieuLeDTO>();
            while (dr.Read())
            {
                tempDTO = new DieuLeDTO();
                tempDTO.SoDieuLe = dr["SoDieuLe"].ToString();
                dsDIEULE.Add(tempDTO);
            }
            return dsDIEULE;
        }

        public List<DieuLeDTO> LayDanhSachSoDieuLe()
        {
            String sql = "Select * From DIEULE ";
            return LayDSachSoDieuLe(sql);
        }

        public List<DieuLeDTO> LayDanhSachSoDieuLeTheoMaChuong(int MaChuong)
        {
            String sql = "Select * From DIEULE Where MaChuong = " + MaChuong;
            return LayDSachSoDieuLe(sql);
        }
      
        #endregion


        #region "Lấy danh sách nội dung"
        private List<DieuLeDTO> LayDSachNoiDung(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DieuLeDTO tempDTO;
            List<DieuLeDTO> dsDIEULE = new List<DieuLeDTO>();
            while (dr.Read())
            {
                tempDTO = new DieuLeDTO();
                tempDTO.NoiDung = dr["NoiDung"].ToString();
                dsDIEULE.Add(tempDTO);
            }
            return dsDIEULE;
        }

        public List<DieuLeDTO> LayDanhSachNoiDung()
        {
            String sql = "Select * From DIEULE ";
            return LayDSachNoiDung(sql);
        }

        public List<DieuLeDTO> LayDanhSachNoiDungDieuLeTheoMaChuong(int MaChuong)
        {
            String sql = "Select * From DIEULE Where MaChuong = " + MaChuong;
            return LayDSachNoiDung(sql);
        }
      
        #endregion
        

        #region "Tìm kiếm"
        public DieuLeDTO TimTheoMa(int MaDieuLe)
        {

            string strSQL = "select * from DIEULE Where IDDieuLe=" + MaDieuLe.ToString();
            return LayDoiTuong(strSQL);
        }
        #endregion


        /////////////////////
        //#region "Thêm"
        //public void Them(DieuLeDTO tempDTO)
        //{
        //    OleDbConnection cn = this.Connect();
        //    string strSQL = "insert into DIEULE(TenDieuLe, SoDieuLe, NoiDung, IDChuong) values (@TenDieuLe, @SoDieuLe, @NoiDung, @IDChuong)";
        //    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
        //    cmd.Parameters.Add("@TenDieuLe", OleDbType.LongVarWChar).Value = tempDTO.TenDieuLe;
        //    cmd.Parameters.Add("@SoDieuLe", OleDbType.LongVarWChar).Value = tempDTO.SoDieuLe;
        //    cmd.Parameters.Add("@NoiDung", OleDbType.LongVarWChar).Value = tempDTO.NoiDung;
        //    cmd.Parameters.Add("@IDChuong", OleDbType.Integer).Value = tempDTO.IDChuong;
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}
        //#endregion


        //#region "Xóa"
        //public void Xoa(int IDDieuLe)
        //{
        //    OleDbConnection cn = this.Connect();
        //    string strSQL = "delete from DIEULE Where IDDieuLe = " + IDDieuLe.ToString();
        //    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}
        //#endregion


        //#region "Sửa"
        //public void Sua(DieuLeDTO tempDTO)
        //{
        //    OleDbConnection cn = this.Connect();
        //    string strSQL = "update DIEULE set TenDieuLe = @TenDieuLe, SoDieuLe = @SoDieuLe, IDChuong =  @IDChuong where IDDieuLe = @IDDieuLe";
        //    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
        //    cmd.Parameters.Add("@TenDieuLe", OleDbType.LongVarWChar).Value = tempDTO.TenDieuLe;
        //    cmd.Parameters.Add("@SoDieuLe", OleDbType.LongVarWChar).Value = tempDTO.SoDieuLe;
        //    cmd.Parameters.Add("@NoiDung", OleDbType.LongVarWChar).Value = tempDTO.NoiDung;
        //    cmd.Parameters.Add("@IDChuong", OleDbType.Integer).Value = tempDTO.IDChuong;
        //    cmd.Parameters.Add("@IDDieuLe", OleDbType.Integer).Value = tempDTO.IDDieuLe;
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}
        //#endregion

    }
}